import pandas as pd
import numpy as np
data1=pd.read_excel('广西.xlsx')
data2=pd.read_excel('湖北.xlsx')
data3=data1.append(data2)
print(data3)
data4=data3[data3['产品通用名称']=='有机肥料']

#把字符形式变为整数
data4.loc[:,'总无机养分百分比'].astype(int)
data4.loc[:,'有机质百分比'].astype(int)

#求出最大值和最小值
print(data4['总无机养分百分比'].max())
print(data4['总无机养分百分比'].min())

#发现无机标签的有一个最大值超出正常范围,因此此最大值删去最大值
data4=data4.drop(data4[data4['总无机养分百分比']==0.43].index)
print(data4['总无机养分百分比'].max())
print(data4['总无机养分百分比'].min())

#求出最大值和最小值
print(data4['有机质百分比'].max())
print(data4['有机质百分比'].min())

#分成十份
bins1=[0.0500,0.0591,0.0681,0.0771,0.0861,0.0951,0.1041,0.1131,0.1221,0.1311,0.1401]
bins2=[-0.01, 0.09, 0.18, 0.27, 0.36, 0.45, 0.54, 0.63, 0.72, 0.81, 0.90]
labels1=['1','2','3','4','5','6','7','8','9','10']
labels2=['1','2','3','4','5','6','7','8','9','10']
data4['无机标签']=pd.cut(data4['总无机养分百分比'],bins1,labels=labels1)
print(data4['无机标签'].value_counts())
data4['有机标签']=pd.cut(data4['有机质百分比'],bins2,labels=labels2)

#把有机标签和无机标签合并
data4['标签']=data4.apply(lambda x:'('+str(x['无机标签'])+','+str(x['有机标签'])+')',axis=1)
print(data4['标签'].value_counts())
data4.to_excel('result2_2.xlsx',index=False)

#绘制热力图
import pandas as pd
import random
data=pd.read_excel('B题task2.2.2.xlsx')
from pyecharts.charts import HeatMap
from pyecharts import options as opts
cols=[0,1,2,3,4,5,6,7,8,9]
indexs=[0,1,2,3,4,5,6,7,8,9]
data.columns=cols
data.index=indexs

#转化为浮点数数形式
data=data.astype(float)
print(data.info())
columns=['1','2','3','4','5','6','7','8','9','10']
index=['1','2','3','4','5','6','7','8','9','10']
print(data)

#设置数据
value=[[y,x,data.loc[x,y]]for x in range(10)for y in range(10)]
print(value)

heatmap = HeatMap(init_opts=opts.InitOpts())
heatmap.add_xaxis(columns)
heatmap.add_yaxis("", index, value,label_opts=opts.LabelOpts(is_show=True, position="inside"))
heatmap.set_global_opts( title_opts=opts.TitleOpts(title="肥料标签热力图"),
                        visualmap_opts=opts.VisualMapOpts(max_=50,min_=0),         # 视觉映射配置
                        xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts()))      # 将x轴标签旋转
heatmap.render("热力图.html")